﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using UniMana.Data;
using UniMana.Utils.Data;
using UniMana.Utils.Configs;
using UniMana.Utils.Controls;
using UniMana.Utils.LoginInfo;


namespace UniMana.Data
{
    public static class Personnel_AttendanceV2Data
    {
        #region [Hữu]
        //Danh sách chấm công theo phòng ban (TH: 1 người chấm 1 phòng ban)
        public static DataTable GetPersonelAttendance(DBMain m_data, int Year, int Month, int DeptKey)
        {
            DataTable tblResult = new DataTable();
            Dictionary<int, string> dictSymbol = new Dictionary<int, string>();
            //Khởi tại danh sách các ký tự được dùng
            var q = m_data.m_DCReadOnly.Catalogue_Idles.Where(p=>p.V2 == true);
            foreach (var i in q)
            {
                dictSymbol.Add(i.Catalogue_IdleKey, i.ID);
            }

            var att  = m_data.m_DCReadOnly.Personnel_AttendanceV2s.Where(p=>p.Catalogue_DepartmentKey == DeptKey
                                                                                   &&p.Year == Year
                                                                                   &&p.Month== Month).FirstOrDefault();
            
            var qrs = from aD in m_data.m_DCReadOnly.Personnel_Attendance_DetailV2s
                    join a in m_data.m_DCReadOnly.Personnel_AttendanceV2s on aD.Personnel_AttendanceKey equals a.Personnel_AttendanceKey
                    join pr in m_data.m_DCReadOnly.Personnel_Personnels on aD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                    join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on aD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                    where aD.Personnel_AttendanceKey== att.Personnel_AttendanceKey
                    && pr.Personnel_PersonnelTypeKey != 2
                    && pr.Catalogue_PersonnelStatusKey == 1
                    select new
                    {
                        aD.Personnel_PersonnelKey,
                        aD.Catalogue_ShiftsKey,
                        pr.ID,
                        pr.Name,
                        pr.LastName,
                        a.Catalogue_DepartmentKey,
                        #region [26 ngày công]
                        D26 =aD.D26+"",
                        D27=aD.D27+"", 
                        D28=aD.D28+"",
                        D29=aD.D29+"",
                        D30=aD.D30+"",
                        D31=aD.D31+"",
                        D1= aD.D1+"",
                        D2=aD.D2+"", 
                        D3=aD.D3+"",
                        D4=aD.D4+"",
                        D5=aD.D5+"",
                        D6=aD.D6+"",
                        D7=aD.D7+"",
                        D8=aD.D8+"",
                        D9=aD.D9+"",
                        D10=aD.D10+"",
                        D11=aD.D11+"",
                        D12=aD.D12+"",
                        D13=aD.D13+"",
                        D14=aD.D14+"", 
                        D15=aD.D15+"",
                        D16=aD.D16+"",
                        D17=aD.D17+"",
                        D18=aD.D18+"",
                        D19=aD.D19+"",
                        D20=aD.D20+"",
                        D21=aD.D21+"",
                        D22=aD.D22+"",
                        D23=aD.D23+"",
                        D24=aD.D24+"", 
                        D25=aD.D25+"",
                        #endregion
                        AbsentDay = aD.AbsentDay!=null? aD.AbsentDay:0,
                        WorkDay = aD.WorkDay != null ? aD.WorkDay : 0,
                        cwd.ContactWorkDays,
                        TotalWorkDay=0.0,
                        TotalAbsentDay=0.0,
                        NameID="["+pr.ID+"] "+ pr.LastName+" "+pr.Name
                    };
            //MessageBox.Show(qrs.Count().ToString());

            try
            {
                if (qrs.Count() > 0)
                {
                    tblResult = qrs.ToDataTable();
                    var totalWorkDay = m_data.m_DCReadOnly.Personnel_TotalWorkDays.Where(p => p.Personnel_AttendanceKey == att.Personnel_AttendanceKey);
                    foreach (DataRow r in tblResult.Rows)
                    {
                        #region [26 ngày công]
                        r["D26"] = dictSymbol.Where(p => p.Key == int.Parse(r["D26"].ToString())).FirstOrDefault().Value;
                        r["D27"] = dictSymbol.Where(p => p.Key == int.Parse(r["D27"].ToString())).FirstOrDefault().Value;
                        r["D28"] = dictSymbol.Where(p => p.Key == int.Parse(r["D28"].ToString())).FirstOrDefault().Value;
                        r["D29"] = dictSymbol.Where(p => p.Key == int.Parse(r["D29"].ToString())).FirstOrDefault().Value;
                        r["D30"] = dictSymbol.Where(p => p.Key == int.Parse(r["D30"].ToString())).FirstOrDefault().Value;
                        r["D31"] = dictSymbol.Where(p => p.Key == int.Parse(r["D31"].ToString())).FirstOrDefault().Value;
                        r["D1"] = dictSymbol.Where(p => p.Key == int.Parse(r["D1"].ToString())).FirstOrDefault().Value;
                        r["D2"] = dictSymbol.Where(p => p.Key == int.Parse(r["D2"].ToString())).FirstOrDefault().Value;
                        r["D3"] = dictSymbol.Where(p => p.Key == int.Parse(r["D3"].ToString())).FirstOrDefault().Value;
                        r["D4"] = dictSymbol.Where(p => p.Key == int.Parse(r["D4"].ToString())).FirstOrDefault().Value;
                        r["D5"] = dictSymbol.Where(p => p.Key == int.Parse(r["D5"].ToString())).FirstOrDefault().Value;
                        r["D6"] = dictSymbol.Where(p => p.Key == int.Parse(r["D6"].ToString())).FirstOrDefault().Value;
                        r["D7"] = dictSymbol.Where(p => p.Key == int.Parse(r["D7"].ToString())).FirstOrDefault().Value;
                        r["D8"] = dictSymbol.Where(p => p.Key == int.Parse(r["D8"].ToString())).FirstOrDefault().Value;
                        r["D9"] = dictSymbol.Where(p => p.Key == int.Parse(r["D9"].ToString())).FirstOrDefault().Value;
                        r["D10"] = dictSymbol.Where(p => p.Key == int.Parse(r["D10"].ToString())).FirstOrDefault().Value;
                        r["D11"] = dictSymbol.Where(p => p.Key == int.Parse(r["D11"].ToString())).FirstOrDefault().Value;
                        r["D12"] = dictSymbol.Where(p => p.Key == int.Parse(r["D12"].ToString())).FirstOrDefault().Value;
                        r["D13"] = dictSymbol.Where(p => p.Key == int.Parse(r["D13"].ToString())).FirstOrDefault().Value;
                        r["D14"] = dictSymbol.Where(p => p.Key == int.Parse(r["D14"].ToString())).FirstOrDefault().Value;
                        r["D15"] = dictSymbol.Where(p => p.Key == int.Parse(r["D15"].ToString())).FirstOrDefault().Value;
                        r["D16"] = dictSymbol.Where(p => p.Key == int.Parse(r["D16"].ToString())).FirstOrDefault().Value;
                        r["D17"] = dictSymbol.Where(p => p.Key == int.Parse(r["D17"].ToString())).FirstOrDefault().Value;
                        r["D18"] = dictSymbol.Where(p => p.Key == int.Parse(r["D18"].ToString())).FirstOrDefault().Value;
                        r["D19"] = dictSymbol.Where(p => p.Key == int.Parse(r["D19"].ToString())).FirstOrDefault().Value;
                        r["D20"] = dictSymbol.Where(p => p.Key == int.Parse(r["D20"].ToString())).FirstOrDefault().Value;
                        r["D21"] = dictSymbol.Where(p => p.Key == int.Parse(r["D21"].ToString())).FirstOrDefault().Value;
                        r["D22"] = dictSymbol.Where(p => p.Key == int.Parse(r["D22"].ToString())).FirstOrDefault().Value;
                        r["D23"] = dictSymbol.Where(p => p.Key == int.Parse(r["D23"].ToString())).FirstOrDefault().Value;
                        r["D24"] = dictSymbol.Where(p => p.Key == int.Parse(r["D24"].ToString())).FirstOrDefault().Value;
                        r["D25"] = dictSymbol.Where(p => p.Key == int.Parse(r["D25"].ToString())).FirstOrDefault().Value;

                        var q1 = totalWorkDay.Where(p => p.Personnel_PersonnelKey == Guid.Parse(r["Personnel_PersonnelKey"].ToString())).FirstOrDefault();
                        if (q1.TotalWorkDay != null)
                        {
                            r["TotalWorkDay"] = q1.TotalWorkDay;
                        }
                        else
                        {
                            r["TotalWorkDay"] = 0;
                        }

                        if (q1.TotalAbsentDay != null)
                        {
                            r["TotalAbsentDay"] = q1.TotalAbsentDay;
                        }
                        else
                        {
                            r["TotalAbsentDay"] = 0;
                        }
                        #endregion
                    }
                }
            }
            catch { }
            return tblResult;
        }

        //Danh sách chấm công theo phòng ban (TH: Theo từng người - cá nhân tự chấm công)
        public static DataTable GetPersonelAttendanceByUserKey(DBMain m_data, int Year, int Month, int DeptKey, string UserKey)
        {

            DataTable tblResult = new DataTable();
            Dictionary<int, string> dictSymbol = new Dictionary<int, string>();
            //Khởi tại danh sách các ký tự được dùng
            var q = m_data.m_DCReadOnly.Catalogue_Idles.Where(p => p.V2 == true);
            foreach (var i in q)
            {
                dictSymbol.Add(i.Catalogue_IdleKey, i.ID);
            }

            var att = m_data.m_DCReadOnly.Personnel_AttendanceV2s.Where(p => p.Catalogue_DepartmentKey == DeptKey
                                                                       && p.Year == Year
                                                                       && p.Month == Month).FirstOrDefault();

            var qrs = from aD in m_data.m_DCReadOnly.Personnel_Attendance_DetailV2s
                    join a in m_data.m_DCReadOnly.Personnel_AttendanceV2s on aD.Personnel_AttendanceKey equals a.Personnel_AttendanceKey
                    join pr in m_data.m_DCReadOnly.Personnel_Personnels on aD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                    join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on aD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                    where aD.Personnel_AttendanceKey == att.Personnel_AttendanceKey
                    && pr.Personnel_PersonnelTypeKey != 2
                    && pr.Catalogue_PersonnelStatusKey == 1
                    where pr.Personnel_PersonnelKey == Guid.Parse(UserKey)
                    orderby pr.Name
                    select new
                    {
                        aD.Personnel_PersonnelKey,
                        aD.Catalogue_ShiftsKey,
                        pr.ID,
                        pr.Name,
                        pr.LastName,
                        a.Catalogue_DepartmentKey,
                        #region [26 ngày công]
                        D26 = aD.D26 + "",
                        D27 = aD.D27 + "",
                        D28 = aD.D28 + "",
                        D29 = aD.D29 + "",
                        D30 = aD.D30 + "",
                        D31 = aD.D31 + "",
                        D1 = aD.D1 + "",
                        D2 = aD.D2 + "",
                        D3 = aD.D3 + "",
                        D4 = aD.D4 + "",
                        D5 = aD.D5 + "",
                        D6 = aD.D6 + "",
                        D7 = aD.D7 + "",
                        D8 = aD.D8 + "",
                        D9 = aD.D9 + "",
                        D10 = aD.D10 + "",
                        D11 = aD.D11 + "",
                        D12 = aD.D12 + "",
                        D13 = aD.D13 + "",
                        D14 = aD.D14 + "",
                        D15 = aD.D15 + "",
                        D16 = aD.D16 + "",
                        D17 = aD.D17 + "",
                        D18 = aD.D18 + "",
                        D19 = aD.D19 + "",
                        D20 = aD.D20 + "",
                        D21 = aD.D21 + "",
                        D22 = aD.D22 + "",
                        D23 = aD.D23 + "",
                        D24 = aD.D24 + "",
                        D25 = aD.D25 + "",
                        #endregion
                        AbsentDay = aD.AbsentDay != null ? aD.AbsentDay : 0,
                        WorkDay = aD.WorkDay != null ? aD.WorkDay : 0,
                        cwd.ContactWorkDays,
                        TotalWorkDay =0.0,
                        TotalAbsentDay =0.0,
                        NameID = "[" + pr.ID + "] " + pr.LastName + " " + pr.Name
                    };

            try
            {
                if (qrs.Count() > 0)
                {
                    tblResult = qrs.ToDataTable();
                    var totalWorkDay = m_data.m_DCReadOnly.Personnel_TotalWorkDays.Where(p => p.Personnel_AttendanceKey == att.Personnel_AttendanceKey);
                    foreach (DataRow r in tblResult.Rows)
                    {
                        #region [26 ngày công]
                        r["D26"] = dictSymbol.Where(p => p.Key == int.Parse(r["D26"].ToString())).FirstOrDefault().Value;
                        r["D27"] = dictSymbol.Where(p => p.Key == int.Parse(r["D27"].ToString())).FirstOrDefault().Value;
                        r["D28"] = dictSymbol.Where(p => p.Key == int.Parse(r["D28"].ToString())).FirstOrDefault().Value;
                        r["D29"] = dictSymbol.Where(p => p.Key == int.Parse(r["D29"].ToString())).FirstOrDefault().Value;
                        r["D30"] = dictSymbol.Where(p => p.Key == int.Parse(r["D30"].ToString())).FirstOrDefault().Value;
                        r["D31"] = dictSymbol.Where(p => p.Key == int.Parse(r["D31"].ToString())).FirstOrDefault().Value;
                        r["D1"] = dictSymbol.Where(p => p.Key == int.Parse(r["D1"].ToString())).FirstOrDefault().Value;
                        r["D2"] = dictSymbol.Where(p => p.Key == int.Parse(r["D2"].ToString())).FirstOrDefault().Value;
                        r["D3"] = dictSymbol.Where(p => p.Key == int.Parse(r["D3"].ToString())).FirstOrDefault().Value;
                        r["D4"] = dictSymbol.Where(p => p.Key == int.Parse(r["D4"].ToString())).FirstOrDefault().Value;
                        r["D5"] = dictSymbol.Where(p => p.Key == int.Parse(r["D5"].ToString())).FirstOrDefault().Value;
                        r["D6"] = dictSymbol.Where(p => p.Key == int.Parse(r["D6"].ToString())).FirstOrDefault().Value;
                        r["D7"] = dictSymbol.Where(p => p.Key == int.Parse(r["D7"].ToString())).FirstOrDefault().Value;
                        r["D8"] = dictSymbol.Where(p => p.Key == int.Parse(r["D8"].ToString())).FirstOrDefault().Value;
                        r["D9"] = dictSymbol.Where(p => p.Key == int.Parse(r["D9"].ToString())).FirstOrDefault().Value;
                        r["D10"] = dictSymbol.Where(p => p.Key == int.Parse(r["D10"].ToString())).FirstOrDefault().Value;
                        r["D11"] = dictSymbol.Where(p => p.Key == int.Parse(r["D11"].ToString())).FirstOrDefault().Value;
                        r["D12"] = dictSymbol.Where(p => p.Key == int.Parse(r["D12"].ToString())).FirstOrDefault().Value;
                        r["D13"] = dictSymbol.Where(p => p.Key == int.Parse(r["D13"].ToString())).FirstOrDefault().Value;
                        r["D14"] = dictSymbol.Where(p => p.Key == int.Parse(r["D14"].ToString())).FirstOrDefault().Value;
                        r["D15"] = dictSymbol.Where(p => p.Key == int.Parse(r["D15"].ToString())).FirstOrDefault().Value;
                        r["D16"] = dictSymbol.Where(p => p.Key == int.Parse(r["D16"].ToString())).FirstOrDefault().Value;
                        r["D17"] = dictSymbol.Where(p => p.Key == int.Parse(r["D17"].ToString())).FirstOrDefault().Value;
                        r["D18"] = dictSymbol.Where(p => p.Key == int.Parse(r["D18"].ToString())).FirstOrDefault().Value;
                        r["D19"] = dictSymbol.Where(p => p.Key == int.Parse(r["D19"].ToString())).FirstOrDefault().Value;
                        r["D20"] = dictSymbol.Where(p => p.Key == int.Parse(r["D20"].ToString())).FirstOrDefault().Value;
                        r["D21"] = dictSymbol.Where(p => p.Key == int.Parse(r["D21"].ToString())).FirstOrDefault().Value;
                        r["D22"] = dictSymbol.Where(p => p.Key == int.Parse(r["D22"].ToString())).FirstOrDefault().Value;
                        r["D23"] = dictSymbol.Where(p => p.Key == int.Parse(r["D23"].ToString())).FirstOrDefault().Value;
                        r["D24"] = dictSymbol.Where(p => p.Key == int.Parse(r["D24"].ToString())).FirstOrDefault().Value;
                        r["D25"] = dictSymbol.Where(p => p.Key == int.Parse(r["D25"].ToString())).FirstOrDefault().Value;

                        var q1 = totalWorkDay.Where(p => p.Personnel_PersonnelKey == Guid.Parse(r["Personnel_PersonnelKey"].ToString())).FirstOrDefault();
                        if (q1.TotalWorkDay != null)
                        {
                            r["TotalWorkDay"] = q1.TotalWorkDay;
                        }
                        else
                        {
                            r["TotalWorkDay"] = 0;
                        }

                        if (q1.TotalAbsentDay != null)
                        {
                            r["TotalAbsentDay"] = q1.TotalAbsentDay;
                        }
                        else
                        {
                            r["TotalAbsentDay"] = 0;
                        }
                        #endregion
                    }
                }
            }
            catch { }
            return tblResult;
        }

        //Danh sách chấm công theo phòng ban (TH: 1 người chấm nhiều phòng ban)
        public static DataTable GetPersonelMultiAttendance(DBMain m_data, int Year, int Month, List<int?> deptList)
        {
            DataTable tblResult = new DataTable();
            Dictionary<int, string> dictSymbol = new Dictionary<int, string>();
            //Khởi tại danh sách các ký tự được dùng
            var q = m_data.m_DCReadOnly.Catalogue_Idles.Where(p => p.V2 == true);
            foreach (var i in q)
            {
                dictSymbol.Add(i.Catalogue_IdleKey, i.ID);
            }

            var qrs = from aD in m_data.m_DCReadOnly.Personnel_Attendance_DetailV2s
                    join a in m_data.m_DCReadOnly.Personnel_AttendanceV2s on aD.Personnel_AttendanceKey equals a.Personnel_AttendanceKey
                    join pr in m_data.m_DCReadOnly.Personnel_Personnels on aD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                    join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on aD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                    where a.Year == Year && a.Month == Month && deptList.Contains(a.Catalogue_DepartmentKey)
                    && pr.Personnel_PersonnelTypeKey !=2
                    && pr.Catalogue_PersonnelStatusKey ==1
                    orderby pr.Name
                    select new
                    {
                        aD.Personnel_PersonnelKey,
                        aD.Catalogue_ShiftsKey,
                        a.Personnel_AttendanceKey,
                        pr.ID,
                        pr.Name,
                        pr.LastName,
                        a.Catalogue_DepartmentKey,
                        #region [26 ngày công]
                        D26 = aD.D26 + "",
                        D27 = aD.D27 + "",
                        D28 = aD.D28 + "",
                        D29 = aD.D29 + "",
                        D30 = aD.D30 + "",
                        D31 = aD.D31 + "",
                        D1 = aD.D1 + "",
                        D2 = aD.D2 + "",
                        D3 = aD.D3 + "",
                        D4 = aD.D4 + "",
                        D5 = aD.D5 + "",
                        D6 = aD.D6 + "",
                        D7 = aD.D7 + "",
                        D8 = aD.D8 + "",
                        D9 = aD.D9 + "",
                        D10 = aD.D10 + "",
                        D11 = aD.D11 + "",
                        D12 = aD.D12 + "",
                        D13 = aD.D13 + "",
                        D14 = aD.D14 + "",
                        D15 = aD.D15 + "",
                        D16 = aD.D16 + "",
                        D17 = aD.D17 + "",
                        D18 = aD.D18 + "",
                        D19 = aD.D19 + "",
                        D20 = aD.D20 + "",
                        D21 = aD.D21 + "",
                        D22 = aD.D22 + "",
                        D23 = aD.D23 + "",
                        D24 = aD.D24 + "",
                        D25 = aD.D25 + "",
                        #endregion
                        AbsentDay = aD.AbsentDay != null ? aD.AbsentDay : 0,
                        WorkDay = aD.WorkDay != null ? aD.WorkDay : 0,
                        cwd.ContactWorkDays,
                        TotalWorkDay =0.0,
                        TotalAbsentDay =0.0,
                        NameID = "[" + pr.ID + "] " +pr.LastName + " " + pr.Name
                    };
            try
            {
                tblResult = qrs.ToDataTable();
                if (tblResult.Rows.Count > 0)
                {
                    foreach (DataRow r in tblResult.Rows)
                    {
                        #region [26 ngày công]
                        r["D26"] = dictSymbol.Where(p => p.Key == int.Parse(r["D26"].ToString())).FirstOrDefault().Value;
                        r["D27"] = dictSymbol.Where(p => p.Key == int.Parse(r["D27"].ToString())).FirstOrDefault().Value;
                        r["D28"] = dictSymbol.Where(p => p.Key == int.Parse(r["D28"].ToString())).FirstOrDefault().Value;
                        r["D29"] = dictSymbol.Where(p => p.Key == int.Parse(r["D29"].ToString())).FirstOrDefault().Value;
                        r["D30"] = dictSymbol.Where(p => p.Key == int.Parse(r["D30"].ToString())).FirstOrDefault().Value;
                        r["D31"] = dictSymbol.Where(p => p.Key == int.Parse(r["D31"].ToString())).FirstOrDefault().Value;
                        r["D1"] = dictSymbol.Where(p => p.Key == int.Parse(r["D1"].ToString())).FirstOrDefault().Value;
                        r["D2"] = dictSymbol.Where(p => p.Key == int.Parse(r["D2"].ToString())).FirstOrDefault().Value;
                        r["D3"] = dictSymbol.Where(p => p.Key == int.Parse(r["D3"].ToString())).FirstOrDefault().Value;
                        r["D4"] = dictSymbol.Where(p => p.Key == int.Parse(r["D4"].ToString())).FirstOrDefault().Value;
                        r["D5"] = dictSymbol.Where(p => p.Key == int.Parse(r["D5"].ToString())).FirstOrDefault().Value;
                        r["D6"] = dictSymbol.Where(p => p.Key == int.Parse(r["D6"].ToString())).FirstOrDefault().Value;
                        r["D7"] = dictSymbol.Where(p => p.Key == int.Parse(r["D7"].ToString())).FirstOrDefault().Value;
                        r["D8"] = dictSymbol.Where(p => p.Key == int.Parse(r["D8"].ToString())).FirstOrDefault().Value;
                        r["D9"] = dictSymbol.Where(p => p.Key == int.Parse(r["D9"].ToString())).FirstOrDefault().Value;
                        r["D10"] = dictSymbol.Where(p => p.Key == int.Parse(r["D10"].ToString())).FirstOrDefault().Value;
                        r["D11"] = dictSymbol.Where(p => p.Key == int.Parse(r["D11"].ToString())).FirstOrDefault().Value;
                        r["D12"] = dictSymbol.Where(p => p.Key == int.Parse(r["D12"].ToString())).FirstOrDefault().Value;
                        r["D13"] = dictSymbol.Where(p => p.Key == int.Parse(r["D13"].ToString())).FirstOrDefault().Value;
                        r["D14"] = dictSymbol.Where(p => p.Key == int.Parse(r["D14"].ToString())).FirstOrDefault().Value;
                        r["D15"] = dictSymbol.Where(p => p.Key == int.Parse(r["D15"].ToString())).FirstOrDefault().Value;
                        r["D16"] = dictSymbol.Where(p => p.Key == int.Parse(r["D16"].ToString())).FirstOrDefault().Value;
                        r["D17"] = dictSymbol.Where(p => p.Key == int.Parse(r["D17"].ToString())).FirstOrDefault().Value;
                        r["D18"] = dictSymbol.Where(p => p.Key == int.Parse(r["D18"].ToString())).FirstOrDefault().Value;
                        r["D19"] = dictSymbol.Where(p => p.Key == int.Parse(r["D19"].ToString())).FirstOrDefault().Value;
                        r["D20"] = dictSymbol.Where(p => p.Key == int.Parse(r["D20"].ToString())).FirstOrDefault().Value;
                        r["D21"] = dictSymbol.Where(p => p.Key == int.Parse(r["D21"].ToString())).FirstOrDefault().Value;
                        r["D22"] = dictSymbol.Where(p => p.Key == int.Parse(r["D22"].ToString())).FirstOrDefault().Value;
                        r["D23"] = dictSymbol.Where(p => p.Key == int.Parse(r["D23"].ToString())).FirstOrDefault().Value;
                        r["D24"] = dictSymbol.Where(p => p.Key == int.Parse(r["D24"].ToString())).FirstOrDefault().Value;
                        r["D25"] = dictSymbol.Where(p => p.Key == int.Parse(r["D25"].ToString())).FirstOrDefault().Value;

                        var q1 = m_data.m_DCReadOnly.Personnel_TotalWorkDays.Where(p => p.Personnel_PersonnelKey == Guid.Parse(r["Personnel_PersonnelKey"].ToString())
                                                                                                && p.Personnel_AttendanceKey == int.Parse(r["Personnel_AttendanceKey"].ToString()))
                                                                                                .FirstOrDefault();
                      
                        if (q1.TotalWorkDay != null)
                        {
                            r["TotalWorkDay"] = q1.TotalWorkDay;
                        }
                        else
                        {
                            r["TotalWorkDay"] = 0;
                        }

                        if (q1.TotalAbsentDay != null)
                        {
                            r["TotalAbsentDay"] = q1.TotalAbsentDay;
                        }
                        else
                        {
                            r["TotalAbsentDay"] = 0;
                        }
                        #endregion
                    }
                }
            }
            catch { }
            return tblResult;
        }

        //Dữ liệu chấm công thổng hợp theo phòng ban (TH: 1 phòng ban)
        public static IQueryable GetAttendance_Synthesis(DBMain m_data, int Year, int Month, int DeptKey)
        {
           var att = m_data.m_DCReadOnly.Personnel_AttendanceV2s.Where(p => p.Year == Year
                                                                            && p.Month == Month
                                                                            && p.Catalogue_DepartmentKey == DeptKey).FirstOrDefault();
            var q = from TTWD in m_data.m_DCReadOnly.Personnel_TotalWorkDays
                    join pr in m_data.m_DCReadOnly.Personnel_Personnels on TTWD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                    join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on TTWD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                    where TTWD.Personnel_AttendanceKey == att.Personnel_AttendanceKey
                    select new
                    {
                        TTWD.Personnel_PersonnelKey,
                        pr.Catalogue_DegreeKey,
                        pr.ID,
                        pr.Name,
                        pr.LastName,
                        pr.Catalogue_DepartmentKey,
                        TotalWorkDay = TTWD.TotalWorkDay != null ? TTWD.TotalWorkDay : 0.0,
                        TotalAbsentDay = TTWD.TotalAbsentDay != null ? TTWD.TotalAbsentDay : 0.0,
                        ContactWorkDays = cwd.ContactWorkDays != null ? cwd.ContactWorkDays : 0.0,
                        TTWD.Personnel_AttendanceKey,
                        WorkDayTotalFormular = (cwd.ContactWorkDays - TTWD.TotalAbsentDay)>0?(cwd.ContactWorkDays - TTWD.TotalAbsentDay):0
                    };
            return q.OrderBy(p => p.Name);
        }
      
        //Dữ liệu chấm công thổng hợp theo phòng ban (TH: một người tổng hợp nhiều phòng ban)
        public static IQueryable GetAttendance_SynthesisByAppList(DBMain m_data, int Year, int Month, List<int?> DeptKeyList)
        {
                var q = from TTWD in m_data.m_DCReadOnly.Personnel_TotalWorkDays
                        join pr in m_data.m_DCReadOnly.Personnel_Personnels on TTWD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                        join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on TTWD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                        select new
                        {
                            TTWD.Personnel_PersonnelKey,
                            pr.Catalogue_DegreeKey,
                            pr.ID,
                            pr.Name,
                            pr.LastName,
                            pr.Catalogue_DepartmentKey,
                            TTWD.TotalWorkDay,
                            TTWD.TotalAbsentDay,
                            cwd.ContactWorkDays,
                            TTWD.Personnel_AttendanceKey,
                            WorkDayTotalFormular = (cwd.ContactWorkDays - TTWD.TotalAbsentDay) > 0 ? (cwd.ContactWorkDays - TTWD.TotalAbsentDay) : 0
                        };

                List<int?> attList = new List<int?>();
                foreach (int i in DeptKeyList)
                {
                    var att = m_data.m_DCReadOnly.Personnel_AttendanceV2s.Where(p => p.Year == Year
                                                                && p.Month == Month
                                                                && p.Catalogue_DepartmentKey == i).FirstOrDefault();
                    if (att != null)
                    {
                        attList.Add(att.Personnel_AttendanceKey);
                    }
                }

                //Bảng tổng hợp theo đơn vị
                if (DeptKeyList.Count > 0)
                {
                    q = from q1 in q
                        where attList.Contains(q1.Personnel_AttendanceKey)
                        select q1;
                }
                //Bảng tổng hợp toàn trường
                return q.Distinct();
        }

        //Dữ liệu chấm công thổng hợp theo cá nhân
        public static IQueryable GetAttendance_SynthesisByUserKey(DBMain m_data, int Year, int Month, string userKey,int DeptKey = -1)
        {
            var att = m_data.m_DCReadOnly.Personnel_AttendanceV2s.Where(p => p.Year == Year
                                                              && p.Month == Month
                                                              && p.Catalogue_DepartmentKey == DeptKey).FirstOrDefault();

                var q = from TTWD in m_data.m_DCReadOnly.Personnel_TotalWorkDays
                        join pr in m_data.m_DCReadOnly.Personnel_Personnels on TTWD.Personnel_PersonnelKey equals pr.Personnel_PersonnelKey
                        join cwd in m_data.m_DCReadOnly.Personnel_ContactWorkDays on TTWD.Personnel_PersonnelKey equals cwd.Personnel_PersonnelKey
                        where TTWD.Personnel_PersonnelKey == Guid.Parse(userKey)
                        && TTWD.Personnel_AttendanceKey == att.Personnel_AttendanceKey
                        select new
                        {
                            TTWD.Personnel_PersonnelKey,
                            pr.Catalogue_DegreeKey,
                            pr.ID,
                            pr.Name,
                            pr.LastName,
                            pr.Catalogue_DepartmentKey,
                            TTWD.TotalWorkDay,
                            TTWD.TotalAbsentDay,
                            cwd.ContactWorkDays,
                            WorkDayTotalFormular = (cwd.ContactWorkDays - TTWD.TotalAbsentDay) > 0 ? (cwd.ContactWorkDays - TTWD.TotalAbsentDay) : 0
                        };

                return q;
        }

        //Lấy danh sách bảng chấm công tổng hợp theo phòng ban
        public static DataTable GetAttendance_Summary(DBMain m_data, int Year, int DeptKey = -1)
        {
            int[] days = new int[] { 26, 27, 28, 29,30,31,1,2,3,4,5,6,7,8,9,10,11,
                                     12,13,14,15,16,17,18,19,20,21,22,23,24,25};
            DataTable result = new DataTable();
            DataColumn cl1 = new DataColumn("Personnel_PersonnelKey");
            DataColumn cl2 = new DataColumn("ID");
            DataColumn cl3 = new DataColumn("Name");
            DataColumn cl4 = new DataColumn("LastName");
            DataColumn cl5 = new DataColumn("Catalogue_DegreeKey");
            DataColumn cl6 = new DataColumn("Catalogue_DepartmentKey");
            result.Columns.AddRange(new DataColumn[]{cl1,cl2,cl3,cl4,cl5,cl6});

            //Thêm các cột kí hiệu chấm công
            var symbols = m_data.m_DCReadOnly.Catalogue_Idles;
            foreach (var symbol in symbols)
            {
                DataColumn cl = new DataColumn(symbol.ID);
                result.Columns.Add(cl);
            }
            //Cột tổng
            DataColumn clsum = new DataColumn("Sum");
            result.Columns.Add(clsum);

            var q = from attDt in m_data.m_DCReadOnly.Personnel_Attendance_Details
                    join att in m_data.m_DCReadOnly.Personnel_Attendances
                    on attDt.Personnel_AttendanceKey equals att.Personnel_AttendanceKey
                    where att.Year == Year && att.Catalogue_DepartmentKey == DeptKey
                    select attDt;

            //Danh sách nhân viên có chấm công
            var prsKeys = (from s1 in q
                           join prs in m_data.m_DCReadOnly.Personnel_Personnels
                           on s1.Personnel_PersonnelKey equals prs.Personnel_PersonnelKey
                           where prs.Personnel_PersonnelTypeKey !=2
                       select new
                       {
                           s1.Personnel_PersonnelKey,
                           prs.ID,
                           prs.Name,
                           prs.LastName,
                           prs.Catalogue_DegreeKey,
                           prs.Catalogue_DepartmentKey
                       }).Distinct();

            foreach (var prsKey in prsKeys)
            {
                
                DataRow r = result.NewRow();
                r["Personnel_PersonnelKey"] = prsKey.Personnel_PersonnelKey;
                r["ID"] = prsKey.ID;
                r["Name"] = prsKey.Name;
                r["LastName"] = prsKey.LastName;
                r["Catalogue_DegreeKey"] = prsKey.Catalogue_DegreeKey;
                r["Catalogue_DepartmentKey"] = prsKey.Catalogue_DepartmentKey;

                /*foreach (var symbol in symbols)
                {
                    r[symbol.ID] = 0;
                }

                int PCount = 0;
                DataTable qTable = q.Where(p => p.Personnel_PersonnelKey == prsKey.Personnel_PersonnelKey).ToDataTable();
                foreach (DataRow row in qTable.Rows)
                {
                    for (int i = 0; i < days.Count(); i++)
                    {
                        try
                        {
                            string cols = "D" + days[i];
                            string vals = row[cols].ToString();
                            if (vals == "P") { PCount++; r["P"] = PCount;}
                            
                        }
                        catch { }
                    }
                }*/
            }
            return result;
        }
        #endregion
    }
        
}
